package com.litbw.dbbackend;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.litbw.dbbackend.DBManager.FieldType;
import com.litbw.event.Contest;
import com.litbw.event.Event;

public class DBEventAdapter extends AbstractDBAdapter {
	
	/**
	 * The ONE instance of the DBEventAdapter.
	 */
	private static DBEventAdapter _instance = null;
	
	/**
	 * Create and setup a new DBEventAdapter object. NOTE: This class is a
	 * factory implementation, thus the PRIVATE constructor. Use Static
	 * methods to access functionality of this class.
	 */
	private DBEventAdapter() {
		
		super("events");
		
		// Create field setup for table
		Map<String, FieldType> fields = new HashMap<String, FieldType>();
		fields.put("id", FieldType.INTEGER);
		fields.put("location", FieldType.TEXT);
		fields.put("date", FieldType.INTEGER);
		fields.put("time", FieldType.INTEGER);
		fields.put("description", FieldType.TEXT);
		
		super.setTableFields(fields);
		super.setAutoIncrement(false);
		super.setPrimary("id");
		
	}

	/**
	 * Factory model... Ensures there is ONLY one DBEventAdapter present at
	 * any given time.
	 */
	private static void checkAdapter() {
		
		// Make sure the DBManager has an instance.
		if (_instance == null)
			_instance = new DBEventAdapter();
	
	}

	/**
	 * Retrieves a field/value pair list from the specified event.
	 * @param event The event object to build list of.
	 * @return A String containing the comma separated field/value pair
	 * list.
	 */
	private static String fieldValuePairsFromEvent(final Event event) {
		
		String rtn = null;
		
		if (event != null) {
			
			final StringBuilder sb = new StringBuilder();
			//(id, location, date, time, description)
			sb.append("id=");
			sb.append(sqlAcceptable(event.getId()));
			sb.append(", ");
			sb.append("location=");
			sb.append(sqlAcceptable(event.getLocation()));
			sb.append(", ");
			sb.append("date=");
			sb.append(sqlAcceptable(event.getDate()));
			sb.append(", ");
			sb.append("time=");
			sb.append(sqlAcceptable(event.getTime()));
			sb.append(", ");
			sb.append("description=");
			sb.append(sqlAcceptable(event.getDescription()));
			
			rtn = sb.toString();
			
		}
		
		return rtn;
		
	}

	/**
	 * Retrieve a value list from the specified event.
	 * @param event The event object to build list of.
	 * @return A String containing the comma separated value list.
	 */
	private static String valueListFromEvent(final Event event) {
		
		String rtn = null;
		
		if (event != null) {
			
			final StringBuilder sb = new StringBuilder();
			
			sb.append(sqlAcceptable(event.getId()));
			sb.append(", ");
			sb.append(sqlAcceptable(event.getLocation()));
			sb.append(", ");
			sb.append(sqlAcceptable(event.getDate().toString()));
			sb.append(", ");
			sb.append(sqlAcceptable(event.getTime()));
			sb.append(", ");
			sb.append(sqlAcceptable(event.getDescription()));
			
			rtn = sb.toString();
			
		}
		
		return rtn;
		
	}
	
	/**
	 * Store an Event in the Event Table of the database (and it's corresponding
	 * Contests).
	 * @param event The Event object to store.
	 * @return True if the event and ALL corresponding Contests were successfully
	 * stored, false otherwise.
	 */
	public static boolean addEvent(final Event event) {
		
		boolean rtn = false;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection() && event != null) {
			
			// Build the sql statement.
			final String sql = "INSERT INTO " + _instance.table_name  +
					" (id, location, date, time, description) values (" +
					   valueListFromEvent(event) + ")";
			
			// Run the statement
			if (_instance.db.runUpdate(sql) > 0) {
				
				// Update the contests.
				for (Contest c : event.getCategories())
					rtn = rtn & DBContestAdapter.addContest(c);
				
			}
				
			
			// Disconnect from database and change return status.
			_instance.disconnect();
			
		}
		
		return rtn;
		
	}
	
	/**
	 * Delete an Event from the Event Table with the specified id.
	 * @param id The ID of the Event to delete.
	 * @return True if the Event, with the given id, is NO LONGER PRESENT at the
	 * end of the method call, false otherwise (or on error).
	 */
	public static boolean deleteEvent(final int id) {
		
		boolean rtn = false;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection()) {
			
			// Save an instance of the Event from the database before we
			// delete it (used below)
			Event tmp = getEvent(id);
			if (tmp == null) {
				System.err.println("could not retrieve event for contest removal; contests may still be in database");
				return false;
			}
			
			// Build the sql statement.
			final String sql = "DELETE FROM " + _instance.table_name  +
					" WHERE id=" + id;
			
			// Run the statement
			if (_instance.db.runUpdate(sql) > 0) {
			
				// Delete contests which were hosted by this event.
				for (Contest c : tmp.getCategories())	
					rtn = rtn & DBContestAdapter.deleteContest(c.getId());
			
			}
				
			// Disconnect from database and change return status.
			_instance.disconnect();
			
		}
		
		return rtn;
		
	}

	/**
	 * Retrieve a list of all events currently stored in the Events table, or
	 * null if there was an error or no events have been found.
	 * @return An array of Event objects which were stored in the Events Table.
	 */
	public static Event[] getAllEvents() {
		
		Event[] rtn = null;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection()) {
			
			// Build the sql statement.
			final String sql = "SELECT * FROM " + _instance.table_name;

			final ResultSet rslt = _instance.db.runQuery(sql);
			
			rtn = buildEventsFromResult(rslt);
			
		}
		
		return rtn;
		
	}

	/**
	 * Retrieve an Event object, from the Events table, with the specified
	 * id.
	 * @param id The id of the required Event.
	 * @return The desired Event object or null if not found or an error
	 * Occurred.
	 */
	public static Event getEvent(final int id) {
		
		Event rtn = null;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection()) {
			
			// Build the sql statement.
			final String sql = "SELECT * FROM " + _instance.table_name +
					" WHERE id=" + id;
			
			final ResultSet rslt = _instance.db.runQuery(sql);
	
			final Event[] retrieved = buildEventsFromResult(rslt);
			
			if (retrieved != null)
				rtn = retrieved[0];
			
		}
		
		return rtn;
		
	}
	
	/**
	 * Build an array of Event objects which were returned by a query.
	 * @param set A ResultSet from an SQL query.
	 * @return An array of Event objects or null if none.
	 */
	private static Event[] buildEventsFromResult(final ResultSet set) {
		
		Event[] events = null;
		
		if (set != null) {
			
			final List<Event> retrieved = new ArrayList<Event>();
			
			try {
				
				while (set.next()) {
					
					// Parse results. Second try/catch block will ensure that
					// only un buildable events are skipped.
					try {
							
						// Gather data stored directly inside the Event table.
						Event current = new Event();
						current.setDate( set.getString("date") );
						current.setDescription( set.getString("description") );
						current.setId( set.getInt("id") );
						current.setLocation( set.getString("location") );
						current.setTime( set.getInt("time") );
		
						// Get the contests for this event.
						Contest[] contests = DBContestAdapter.getContestsForEvent(Integer.valueOf(current.getId()));
						if (contests != null)
							for (Contest c : contests)
								current.addCategory(c);
					
						retrieved.add(current);
						
					} catch (SQLException sqle) { }
					
				}
				
			} catch (SQLException sqle) {
				sqle.printStackTrace();
			}
			
			if (retrieved != null && !retrieved.isEmpty())
				events = retrieved.toArray(new Event[0]);
			
		}
		
		return events;
		
	}
	
	/**
	 * Update the Event in the Event Table where the id is specified. This also
	 * updates the Contests Table as well.
	 * @param event The Event info to update.
	 * @return True if the Event and corresponding Contests were successfully
	 * updated, false otherwise.
	 */
	public static boolean updateEvent(final Event event) {
		
		boolean rtn = false;
		
		checkAdapter();
		
		if (_instance.checkDatabaseConnection()) {
			
			// Build the sql statement.
			final String sql = "UPDATE " + _instance.table_name  +
					" SET " + fieldValuePairsFromEvent(event) +
					" WHERE id=" + event.getId();
			
			// Run the statement
			if (_instance.db.runUpdate(sql) > 0)
				rtn = true;
			
			// Disconnect from database and change return status.
			_instance.disconnect();
			
		}
		
		return rtn;
		
	}
	
}
